﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data.OleDb;

namespace Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 带路径的Excel地址
        /// </summary>
        private string strFileName;

        private List<string> lstSheetName = new List<string>();

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="FileName">带路径的Excel地址</param>
        public ExcelHelper(string FileName)
        {
            this.FlieName = FileName;
        }
        #endregion

        #region get/set函数
        /// <summary>
        /// get/set函数
        /// </summary>
        public string FlieName
        {
            get
            {
                return this.strFileName;
            }
            set
            {
                if (!string.IsNullOrEmpty(value))
                {
                    //判断是否是Excel文件
                    if (!value.ToLower().EndsWith("xls"))
                        throw new Exception("导入的文件不是excel格式！");
                    //判断文件是否存在
                    if (!File.Exists(value))
                        throw new Exception("导入的文件不存在！");

                    this.strFileName = value;
                    //读入Excel并解析出Sheets名称
                    OleDbConnection conn = null;
                    try
                    {
                        conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'");
                        conn.Open();
                        DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        conn.Close();
                        foreach (DataRow dr in sheetNames.Rows)
                            lstSheetName.Add(dr[2].ToString());
                        if (lstSheetName.Count == 0)
                            throw new Exception("无法解析出sheet或excel内容为空");
                    }
                    catch (OleDbException err)
                    {
                        throw new Exception("读excel的sheeet时出错,错误描述:" + err.ToString());
                    }
                    finally
                    {
                        if (conn != null)
                            conn.Close();
                    }
                }
                else
                    throw new Exception("导入的文件名不能为空或null");
            }
        }
        #endregion

        #region DataTabel生成Excel表格
        /// <summary>
        /// DataTabel生成Excel表格
        /// </summary>
        /// <param name="dt">数据源（datatable）</param>
        /// <param name="generatePath">生成文件所放的路径</param>
        /// <param name="modelPath">模板的路径</param>
        /// <param name="keyList">模板值所对应的数据源的列名称(必须是按顺序)</param>
        /// <param name="rowMove">数据的行偏移量</param>
        /// <param name="sheetIndex">Excel第几个选项卡(从0开始)</param>
        /// <returns>异常信息（为空表示成功）</returns>
        public static string DataTableToExcel(DataTable dt, string generatePath, string modelPath, List<string> keyList, int rowMove, int sheetIndex)
        {
            string msg = "";
            #region Check
            if (dt == null || dt.Rows.Count <= 0)
            {
                msg = "没有任何数据，生成Excel失败";
                return msg;
            }
            if (string.IsNullOrEmpty(generatePath))
            {
                msg = "未指定生成路径";
                return msg;
            }
            if (string.IsNullOrEmpty(generatePath))
            {
                msg = "未指定模板路径";
                return msg;
            }
            if (keyList == null || keyList.Count <= 0)
            {
                msg = "未指定模板值";
                return msg;
            }
            #endregion

            try
            {
                HSSFWorkbook book = null;
                using (FileStream stream = new FileStream(modelPath, FileMode.Open, FileAccess.Read))
                {
                    book = new HSSFWorkbook(stream);
                }
                HSSFSheet sheet = book.GetSheetAt(sheetIndex);
                if (sheet == null)
                {
                    msg = "模板是空的！";
                    Logger.WriteLog(msg);
                    return msg;
                }
                HSSFRow row = null;       //写数据的行偏移量
                foreach (DataRow dr in dt.Rows)
                {
                    //创建
                    row = sheet.CreateRow(rowMove);
                    int j = 0;
                    foreach (string str in keyList)
                    {
                        row.CreateCell(j).SetCellValue(dr[str].ToString());
                        j++;
                    }
                    rowMove++;
                }

                generatePath = generatePath.Replace("/", "\\");

                string DirectoryPath = generatePath.Substring(0, generatePath.LastIndexOf("\\") + 1);

                if (!Directory.Exists(DirectoryPath) && !DirectoryPath.Contains("xls"))
                    Directory.CreateDirectory(DirectoryPath);

                using (FileStream stream = new FileStream(generatePath, FileMode.Create))
                {
                    book.Write(stream);
                    stream.Close();
                }
            }
            catch (Exception ex)
            {
                msg = String.Format("生成Excel失败，请重试，多次失败请联系研发[错误信息：{0}]", ex.Message);
                Logger.WriteLog(msg, ex);
            }
            return msg;
        }
        #endregion

        #region 将Excel的首个Sheet转换成DataTable
        /// <summary>
        /// 将Excel的首个Sheet转换成DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable TruncateFirstSheetToDataTable()
        {
            OleDbConnection conn = null;
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                conn = new OleDbConnection(strConn);
                conn.Open();
                string strFirstSheet = lstSheetName[0];
                OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + lstSheetName[0] + "]", conn);
                DataSet ds = new DataSet();
                oada.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception err)
            {
                throw new Exception("从excel中读取第一个sheet的内容入datatable出错，错误：" + err.ToString());
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }
        #endregion
    }
}
